/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import models.Storage;
import models.StorageItem;

/**
 *
 * @author Dan
 */
public class StorageItemDb extends AbstractDb implements StorageItemInterface {

    private ArrayList<StorageItem> si = new ArrayList<StorageItem>();
        
    public ArrayList<StorageItem> getLentItems(int personID) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int addIteam(ArrayList si) {
        this.si = si;
        int success = 0;
        this.newTransaction();
        for (StorageItem sItem : this.si) {
            String insertQuery = "INSERT INTO Lif_StorageItem (storageItem_storageid, storageItem_serialnumber, storageItem_amount, storageItem_lostAmount)  VALUES("
                    + "" + sItem.getStorageId() + ","
                    + "'" + sItem.getSerialnumber() + "',"
                    + "" + sItem.getAmount() + ","
                    + "" + sItem.getLostAmount() + ");";
            this.addStatement(insertQuery);
        }
        if (this.commitTransaction()) {
            success = 1;
        }
        return success;
    }

    public HashMap<String, String> getExempelAmount() {

        ResultSet items = this.db.query("SELECT storageItem_storageid, SUM(storageItem_amount) AS sum FROM Lif_StorageItem GROUP BY storageItem_storageid");
        HashMap<String, String> list = new HashMap<String, String>();
        try {
            while (items.next()) {
                try {

                    String storageid = items.getString("storageItem_storageid");
                    String sum = String.valueOf(items.getInt("sum"));
                    list.put(storageid, sum);
                } catch (Exception ex) {
                    System.out.println("Der skete en fejl med at tilføje til hasmap!");
                }
            }
        } catch (SQLException ex) {
            System.out.println("while statement døde!");
        }
        return list;
    }

    public ArrayList<StorageItem> getAllRentabelItems() {
        ArrayList<StorageItem> storageItemList = new ArrayList<StorageItem>();
        try {
            ResultSet items = this.db.query("SELECT * FROM Lif_Storage, Lif_StorageItem LEFT JOIN Lif_Rental ON storageItem_id = rental_storageItemid WHERE storage_id = storageItem_storageid");
            if (items != null) {
                while (items.next() && items.getInt("rental_storageItemid") <= 0) {
                    Storage stoObj = new Storage(items.getString("storage_name"), items.getString("storage_description"), items.getInt("storage_amount"));
                    StorageItem storageItem = new StorageItem(stoObj,
                            items.getInt("storageItem_id"),
                            items.getInt("storageItem_amount"),
                            items.getInt("storageItem_lostAmount"),
                            items.getString("storageItem_serialnumber"));
                    storageItemList.add(storageItem);
                }
            }
        } catch (Exception e) {
            System.out.println("Der var en fejl så tingene blev ikke hentet.");
        }
        return storageItemList;

    }

    public ArrayList<StorageItem> getRentabelItems(int Id) {
        ArrayList<StorageItem> storageItemList = new ArrayList<StorageItem>();
        try {
            ResultSet items = this.db.query("SELECT * FROM Lif_Storage, Lif_StorageItem LEFT JOIN Lif_Rental ON storageItem_id = rental_storageItemid WHERE storage_id = storageItem_storageid AND storage_id=" + Id);
            if (items != null) {
                while (items.next() && items.getInt("rental_storageItemid") <= 0) {
                    Storage stoObj = new Storage(items.getString("storage_name"), items.getString("storage_description"), items.getInt("storage_amount"));
                    StorageItem storageItem = new StorageItem(stoObj,
                            items.getInt("storageItem_id"),
                            items.getInt("storageItem_amount"),
                            items.getInt("storageItem_lostAmount"),
                            items.getString("storageItem_serialnumber"));
                    storageItemList.add(storageItem);
                }
            }
        } catch (Exception e) {
            System.out.println("Der var en fejl så tingene blev ikke hentet.");
        }
        return storageItemList;
    }

    public ArrayList<StorageItem> searchStorageItem(String name, String serienummer) {
        ArrayList<StorageItem> storageItemList = new ArrayList<StorageItem>();
        String queryWhere = "";
        try {
            if (name.length() > 0 || serienummer.length() > 0) {
                queryWhere += "WHERE";
                if (name.length() > 0) {
                    queryWhere += " storage_name LIKE '" + name + "%'";
                }
                if (serienummer.length() > 0) {
                    queryWhere += (queryWhere.length() > 5 ? " AND " : "") + " storageItem_serialnumber LIKE '" + serienummer + "%'";
                }
            }
            ResultSet items = this.db.query("SELECT * FROM Lif_Storage LEFT JOIN Lif_StorageItem ON storageItem_storageid = storage_id " + queryWhere);
            if (items != null) {
                while (items.next()) {
                    Storage stoObj = new Storage(items.getInt("storage_id"), items.getString("storage_name"), items.getString("storage_description"), items.getInt("storage_amount"));
                    if (items.getInt("storageItem_id") > 0) {
                        StorageItem storageItem = new StorageItem(stoObj,
                                items.getInt("storageItem_id"),
                                items.getInt("storageItem_amount"),
                                items.getInt("storageItem_lostAmount"),
                                items.getString("storageItem_serialnumber"));
                        storageItemList.add(storageItem);
                    } else {
                        StorageItem storageItem = new StorageItem(stoObj, 0, 0, 0, "");
                        storageItemList.add(storageItem);
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("Der var en fejl så tingene blev ikke hentet.");
        }
        return storageItemList;
    }

    public boolean deletedItem(int id) {
        int rc = -1;
        boolean succeed = false;
        try { // delete from supplier
            String query = "DELETE FROM Lif_StorageItem "
                    + "WHERE storageItem_id = '" + id + "'";
            rc = this.db.queryUpdate(query);
            //end try
            if (rc > -1) {
                succeed = true;
            }
        } catch (Exception ex) {
            System.out.println("storageItem db Delete exception: " + ex);
        }
        return (succeed);
    }

    public boolean editItem(StorageItem sto) {
        StorageItem stoItemObj = sto;
        boolean succes = false;
        if (stoItemObj.getStorageId() > 0) {
            try { // Update supplier
                int rc = this.db.queryUpdate("UPDATE Lif_StorageItem SET "
                        + "storageitem_serialnumber='" + stoItemObj.getSerialnumber() + "', "
                        + "storageitem_amount='" + stoItemObj.getAmount() + "', "
                        + "storageitem_lostAmount='" + stoItemObj.getLostAmount() + "' WHERE storageitem_id =" + stoItemObj.getStorageId() + ";");
                if (rc > 0) {
                    succes = true;
                }
            }//end try
            catch (Exception ex) {
                System.out.println("Item was not Update");
                System.out.println("Update exception in storage item db: " + ex);
            }
        }
        else
        {
            si.add(stoItemObj);
        }
        return succes;
    }
    
    public ArrayList<StorageItem> getAllItems()
    {
        ArrayList<StorageItem> storageItemList = new ArrayList<StorageItem>();
          try {
            ResultSet items = this.db.query("SELECT * FROM Lif_Storage, Lif_StorageItem WHERE storage_id = storageItem_storageid");
            if (items != null) {
                while (items.next()) {
                    Storage stoObj = new Storage(items.getString("storage_name"), items.getString("storage_description"), items.getInt("storage_amount"));
                    StorageItem storageItem = new StorageItem(stoObj,
                            items.getInt("storageItem_id"),
                            items.getInt("storageItem_amount"),
                            items.getInt("storageItem_lostAmount"),
                            items.getString("storageItem_serialnumber"));
                    storageItemList.add(storageItem);
                }
            }
        } catch (Exception e) {
            System.out.println("Der var en fejl så tingene blev ikke hentet.");
        }
        return storageItemList;
    }
}
